#! /usr/bin/perl
use MIME::WordDecoder;
use DBI;
use Date::Parse;

# database information
$db="stats_syslog";
$host="localhost";
$port="10019";
$userid="";
$passwd="";
$connectionInfo="DBI:mysql:database=$db;$host:$port";

my ($ig1,$ig2,$ig3,$day,$month,$year) = localtime time;

# make connection to database
$dbh = DBI->connect($connectionInfo,$userid,$passwd,{RaiseError => 0, PrintError => 0}) || die "Database connection not made: $DBI::errstr";

$date = sprintf("%04d-%02d-%02d", $year+1900, $month+1, $day);

#ACTIONS ALL
$query = "SELECT Cluster, Action, COUNT(*) AS number FROM maillog WHERE timestamp>=DATE_ADD('$date',INTERVAL -1 DAY) AND timestamp<=DATE_ADD('$date 23:59:59',INTERVAL -1 DAY) GROUP BY Cluster, Action";
$sth = $dbh->prepare($query);
$sth->execute();

while (($Cluster,$Action,$number) = $sth->fetchrow_array()) {
	$query = "INSERT INTO actions_all VALUES (DATE_ADD('$date',INTERVAL -1 DAY), '$Cluster', '$Action', '$number')";
	$sth2 = $dbh->prepare($query);
	$sth2->execute();
}

#REASONS ALL
$query = "SELECT Cluster, Reason, COUNT(*) AS number FROM maillog WHERE timestamp>=DATE_ADD('$date',INTERVAL -1 DAY) AND timestamp<=DATE_ADD('$date 23:59:59',INTERVAL -1 DAY) GROUP BY Cluster, Reason";
$sth = $dbh->prepare($query);
$sth->execute();

while (($Cluster,$Reason,$number) = $sth->fetchrow_array()) {
	$query = "INSERT INTO reasons_all VALUES (DATE_ADD('$date',INTERVAL -1 DAY), '$Cluster', '$Reason', '$number')";
	$sth2 = $dbh->prepare($query);
	$sth2->execute();
}

#ACTIONS DOMAIN
$query = "SELECT Cluster, maillog.Domain, Action, COUNT(*) AS number FROM maillog, domains WHERE domains.domain=maillog.Domain AND timestamp>=DATE_ADD('$date',INTERVAL -1 DAY) AND timestamp<=DATE_ADD('$date 23:59:59',INTERVAL -1 DAY) GROUP BY Cluster, maillog.Domain, Action";
$sth = $dbh->prepare($query);
$sth->execute();

while (($Cluster,$Domain,$Action,$number) = $sth->fetchrow_array()) {
	$query = "INSERT INTO actions_domain VALUES (DATE_ADD('$date',INTERVAL -1 DAY), '$Cluster', '$Domain', '$Action', '$number')";
	$sth2 = $dbh->prepare($query);
	$sth2->execute();
}

#REASONS DOMAIN
$query = "SELECT Cluster, maillog.Domain, Reason, COUNT(*) AS number FROM maillog, domains WHERE domains.domain=maillog.Domain AND timestamp>=DATE_ADD('$date',INTERVAL -1 DAY) AND timestamp<=DATE_ADD('$date 23:59:59',INTERVAL -1 DAY) GROUP BY Cluster, maillog.Domain, Reason";
$sth = $dbh->prepare($query);
$sth->execute();

while (($Cluster,$Domain,$Reason,$number) = $sth->fetchrow_array()) {
	$query = "INSERT INTO reasons_domain VALUES (DATE_ADD('$date',INTERVAL -1 DAY), '$Cluster', '$Domain', '$Reason', '$number')";
	$sth2 = $dbh->prepare($query);
	$sth2->execute();
}

#ACTIONS USER
$query = "SELECT Cluster, Recipient, Action, COUNT(*) AS number FROM maillog, users WHERE Recipient=user AND timestamp>=DATE_ADD('$date',INTERVAL -1 DAY) AND timestamp<=DATE_ADD('$date 23:59:59',INTERVAL -1 DAY) GROUP BY Cluster, Recipient, Action";
$sth = $dbh->prepare($query);
$sth->execute();

while (($Cluster,$User,$Action,$number) = $sth->fetchrow_array()) {
	$query = "INSERT INTO actions_user VALUES (DATE_ADD('$date',INTERVAL -1 DAY), '$Cluster', '$User', '$Action', '$number')";
	$sth2 = $dbh->prepare($query);
	$sth2->execute();
}

#REASONS USER
$query = "SELECT Cluster, Recipient, Reason, COUNT(*) AS number FROM maillog, users WHERE Recipient=user AND timestamp>=DATE_ADD('$date',INTERVAL -1 DAY) AND timestamp<=DATE_ADD('$date 23:59:59',INTERVAL -1 DAY) GROUP BY Cluster, Recipient, Reason";
$sth = $dbh->prepare($query);
$sth->execute();

while (($Cluster,$User,$Reason,$number) = $sth->fetchrow_array()) {
	$query = "INSERT INTO reasons_user VALUES (DATE_ADD('$date',INTERVAL -1 DAY), '$Cluster', '$User', '$Reason', '$number')";
	$sth2 = $dbh->prepare($query);
	$sth2->execute();
}

# disconnect from database
$dbh->disconnect;
